package com.coocaa.x.service.litedb;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.RemoteException;

import com.alibaba.fastjson.JSONObject;
import com.coocaa.x.framework.utils.LogUtils;
import com.coocaa.x.service.XServiceManager;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * Created by C0der on 2015/3/11.
 */
public class LiteDBHelper extends SQLiteOpenHelper {

    public LiteDBHelper(Context context, String name) {
        super(context, name, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("create table if not exists table_info(" + "id integer primary key,"
                + "name varchar," + "info varchar)");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    public synchronized boolean createTable(String tablename, String[] fields, String[] types,
                                            int version) {
        Thread.currentThread().setContextClassLoader(getClass().getClassLoader());
        LogUtils.d("table", "createTable  !!!");
        if (hasTable(tablename)) {
            int tabeVersion = 0;
            try {
                tabeVersion = XServiceManager.getLiteConfigService().getIntValue(tablename, 0);
            } catch (RemoteException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
            LogUtils.d("table", "createTable  hasTable  newVersion:" + version + "  oldVersion:"
                    + tabeVersion);
            if (tabeVersion >= version) {
                return false;
            } else {
                updateTable(tablename, fields, types, version);
                return true;
            }
        }
        // 获取一个可写数据库，或者说是以可写的方式打开数据库
        SQLiteDatabase db = getWritableDatabase();
        if (db == null) {
            return false;
        } else {
            // 创建表，先构造一个创建表的SQL语句
            String sql = "CREATE TABLE " + tablename + "(";
            if (types == null) {
                for (int i = 0; i < fields.length; i++) {
                    if (i < fields.length - 1)
                        sql += fields[i] + ", ";
                    if (i == fields.length - 1)
                        sql += fields[i];
                }
            } else {
                for (int i = 0; i < fields.length; i++) {
                    String type = types[i];
                    if (i < fields.length - 1)
                        sql += fields[i] + " " + type + ",";
                    if (i == fields.length - 1)
                        sql += fields[i] + " " + type;
                }
            }
            sql += ")";
            try {
                db.execSQL(sql);
                XServiceManager.getLiteConfigService().saveIntConfig(tablename, version);
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            } finally {
                db.close();
            }
        }
        return true;
    }

    public synchronized boolean hasTable(String tablename) {
        Thread.currentThread().setContextClassLoader(getClass().getClassLoader());
        boolean ret = false;
        SQLiteDatabase db = getReadableDatabase();
        if (db == null) {
            ret = false;
        } else {
            // 表sqlite_master是一个特殊的表，由SQLite自动维护，保存其他表的相关信息
            Cursor cur = db.query("sqlite_master", // 表名
                    new String[]{"name"}, // 列名
                    "name = ?", // where从句，"=="和"="都可以比较
                    new String[]{tablename}, // 对上面的问号赋值
                    null, // groupBy从句
                    null, // having从句
                    "name desc"); // orderBy从句
            if (cur.getCount() > 0)
                ret = true;
            cur.close();
            db.close();
        }
        return ret;
    }

    private synchronized boolean updateTable(String tablename, String[] fields, String[] types,
                                             int version) {
        Thread.currentThread().setContextClassLoader(getClass().getClassLoader());
        SQLiteDatabase db = getReadableDatabase();
        if (db == null) {
            LogUtils.e("table", "updateTable  get db failed!!!!");
            return false;
        } else {
            try {
                // 重命名表
                db.execSQL("ALTER TABLE " + tablename + " RENAME TO  " + "tmp_" + tablename);
                LogUtils.e("table", "updateTable  create temp db success!!!!");
                List<String> ColumnList = getColumnNames("tmp_" + tablename);
                LogUtils.e("table", "updateTable  getColumnNames size:" + ColumnList.size());
                // 创建新表
                String sql = "CREATE TABLE " + tablename + "(";
                if (types == null) {
                    for (int i = 0; i < fields.length; i++) {
                        if (i < fields.length - 1)
                            sql += fields[i] + ", ";
                        if (i == fields.length - 1)
                            sql += fields[i];
                    }
                } else {
                    for (int i = 0; i < fields.length; i++) {
                        String type = types[i];
                        if (i < fields.length - 1)
                            sql += fields[i] + " " + type + ",";
                        if (i == fields.length - 1)
                            sql += fields[i] + " " + type;
                    }
                }
                sql += ")";
                db.execSQL(sql);
                LogUtils.e("table", "updateTable  create table success!!!!");
                // 导入数据
                String sql2 = "INSERT INTO " + tablename + " ( ";
                for (int i = 0; i < fields.length; i++) {
                    if (i < fields.length - 1) {
                        if (ColumnList.contains(fields[i]))
                            sql2 += fields[i] + " ,";
                        // else
                        // sql2 += "\" \"" + ", ";
                    }
                    if (i == fields.length - 1) {
                        if (ColumnList.contains(fields[i]))
                            sql2 += fields[i];
                        // else
                        // sql2.substring(0, sql2.lastIndexOf(","));
                    }
                }
                if (sql2.trim().endsWith(",")) {
                    LogUtils.d("table", "1111111111111  sql2:" + sql2);
                    sql2 = sql2.substring(0, sql2.lastIndexOf(",") - 1);
                    LogUtils.d("table", "2222222222222  sql2:" + sql2);
                }
                sql2 += " ) SELECT ";
                for (int i = 0; i < fields.length; i++) {
                    if (i < fields.length - 1 && ColumnList.contains(fields[i]))
                        sql2 += fields[i] + " ,";
                    if (i == fields.length - 1 && ColumnList.contains(fields[i]))
                        sql2 += fields[i];
                    // else
                    // sql2.substring(0, sql2.lastIndexOf(","));
                }
                if (sql2.trim().endsWith(",")) {
                    LogUtils.d("table", "333333333333  sql2:" + sql2);
                    sql2 = sql2.substring(0, sql2.lastIndexOf(",") - 1);
                    LogUtils.d("table", "444444444444  sql2:" + sql2);
                }
                sql2 += " FROM " + "tmp_" + tablename;
                LogUtils.e("table", "updateTable  copy data sql:" + sql2);
                db.execSQL(sql2);
                XServiceManager.getLiteConfigService().saveIntConfig(tablename, version);
                LogUtils.e("table", "updateTable  copy data success!!!! version:" + version);
                // 删除临时表
                db.execSQL("DROP TABLE " + "tmp_" + tablename);
                LogUtils.e("table", "updateTable  drop temp db success !!!!");
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            } finally {
                db.close();
            }
            return true;
        }
    }

    public synchronized List<String> getColumnNames(String table) {
        Thread.currentThread().setContextClassLoader(getClass().getClassLoader());
        List<String> columnList = new ArrayList<String>();
        try {
            SQLiteDatabase db = getReadableDatabase();
            if (db == null) {
                return columnList;
            } else {
                Cursor cursor = db.query(table, // 表名
                        null, // 列名
                        null, // where从句， ex:"age>?"
                        null, // 对where从句中的问号赋值
                        null, // groupBy从句
                        null, // having从句
                        null); // orderBy从句
                if (cursor != null && cursor.getCount() > 0) {
                    columnList = Arrays.asList(cursor.getColumnNames());
                    LogUtils.d("table", "getColumnNames size:" + columnList.size());
                }
                return columnList;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return columnList;
    }

    public synchronized boolean deleteTable(String tablename) {
        Thread.currentThread().setContextClassLoader(getClass().getClassLoader());
        if (!hasTable(tablename))
            return false;
        SQLiteDatabase db = getReadableDatabase();
        if (db == null) {
            return false;
        } else {
            try {
                db.execSQL("DROP TABLE " + tablename);// 删除数据库中的指定的数据表
            } catch (Exception e) {
                e.printStackTrace();
                return false;
            } finally {
                db.close();
            }
            return true;
        }
    }

    public synchronized boolean insert(String tablename, String[] fields, String[] values) {
        Thread.currentThread().setContextClassLoader(getClass().getClassLoader());
        long time1 = System.currentTimeMillis();
        try {
            SQLiteDatabase db = getWritableDatabase();
            if (db == null) {
                return false;
            } else {
                /**
                 * 关于insert方法的第2个参数，官方解释为： optional; may be null. SQL doesn't allow inserting a
                 * completely empty row without naming at least one column name. If your provided
                 * values is empty, no column names are known and an empty row can't be inserted. If
                 * not set to null, the nullColumnHack parameter provides the name of nullable
                 * column name to explicitly insert a NULL into in the case where your values is
                 * empty.
                 */
                ContentValues contentValues = new ContentValues();
                for (int i = 0; i < values.length; i++) {
                    contentValues.put(fields[i], values[i]);
                }
                db.insert(tablename, null, contentValues);
                db.close();
                return true;
            }
        } finally {
            LogUtils.d("LiteDBHelper", "insert cost:" + (System.currentTimeMillis() - time1));
        }
    }

    public synchronized boolean delete(String tablename, String whereClause, String[] whereArgs) {
        Thread.currentThread().setContextClassLoader(getClass().getClassLoader());
        long time1 = System.currentTimeMillis();
        try {
            SQLiteDatabase db = getWritableDatabase();
            if (db == null) {
                return false;
            } else {
                try {
                    db.delete(tablename, whereClause, whereArgs);
                } catch (Exception e) {
                    e.printStackTrace();
                    return false;
                }
                return true;
            }
        } finally {
            LogUtils.d("LiteDBHelper", "delete cost:" + (System.currentTimeMillis() - time1));
        }
    }

    public synchronized boolean update(String tablename, ContentValues values, String whereClause,
                                       String[] whereArgs) {
        Thread.currentThread().setContextClassLoader(getClass().getClassLoader());
        long time1 = System.currentTimeMillis();
        try {
            SQLiteDatabase db = getWritableDatabase();
            if (db == null) {
                return false;
            } else {
                try {
                    db.update(tablename, values, whereClause, whereArgs);
                } catch (Exception e) {
                    e.printStackTrace();
                    return false;
                }
                return true;
            }
        } finally {
            LogUtils.d("LiteDBHelper", "update cost:" + (System.currentTimeMillis() - time1));
        }
    }

    public synchronized List<String> select(String tablename, String[] fields, String where,
                                            String[] whereArgs, String groupby, String having, String orderby) {
        Thread.currentThread().setContextClassLoader(getClass().getClassLoader());
        List<String> ret = new ArrayList<String>();
        long time1 = System.currentTimeMillis();
        try {
            SQLiteDatabase db = getReadableDatabase();
            if (db == null) {
                return ret;
            } else {
                Cursor cursor = db.query(tablename, // 表名
                        fields, // 列名
                        where, // where从句， ex:"age>?"
                        whereArgs, // 对where从句中的问号赋值
                        groupby, // groupBy从句
                        having, // having从句
                        orderby); // orderBy从句
                if (!cursor.moveToFirst()) {
                    cursor.close();
                    db.close();
                    return ret;
                }
                LinkedHashMap<String, String> map = new LinkedHashMap<String, String>();
                while (true) {
                    for (String field : fields) {
                        String value = cursor.getString(cursor.getColumnIndex(field));
                        map.put(field, value);
                    }
                    ret.add(JSONObject.toJSONString(map));
                    if (!cursor.moveToNext()) {
                        cursor.close();
                        db.close();
                        break;
                    }
                }
                return ret;
            }
        } finally {
            LogUtils.d("LiteDBHelper", "select cost:" + (System.currentTimeMillis() - time1));
        }
    }
}
